﻿-----------------------------------------------------------------------
--
-- 功能:Microsoft SQL Server建立数据库脚本
-- 代码:huangtao117@gmail.com
--
-----------------------------------------------------------------------
if not exists(select * from master..sysdatabases where name='demo')
	create database demo
go

use demo
go

--
-- 表
--

-- 建立用户表
if not exists(select * from sysobjects where id=object_id('t_user'))
create table t_user(
    userid varchar(20) not null,		-- 用户ID
    password varchar(64) not null,      -- 密码
    sex char(2) not null,               -- 性别
    real_name varchar(20),              -- 真实姓名
    identitycard varchar(18),           -- 身份证
    phone varchar(32),                  -- 移动电话
    op_power int default 0,				-- 权限
    group_id int default 0,				-- 社团(团队,战队)ID
    ip_reg varchar(32),                 -- 注册IP
    ip_login varchar(32),               -- 最后登录IP
    sn_reg varchar(64),                 -- 注册终端SN(如pc的机器码)
    sn_login varchar(64),               -- 登录终端SN(如pc的机器码)
    date_reg datetime default getdate(),-- 注册时间戳
    date_login datetime,                -- 最后登录时间戳
    email varchar(64),                  -- 电子邮件
    primary key (userid)
)

-- 社团表
if not exists(select * from sysobjects where id=object_id('t_group'))
create table t_group(
    group_id int not null,			-- 工会ID
    name varchar(64) not null,		-- 工会名称
    master_id varchar(20) not null,	-- 工会管理员ID
    address varchar(64),			-- 工会通讯地址
    score_yz bigint default 0,		-- 工会虚拟积分
    score_exp bigint default 0,		-- 工会经验值
    primary key (group_id)
)

-- 建立虚拟货币表
if not exists(select * from sysobjects where id=object_id('t_userext'))
create table t_userext(
    userid varchar(20) not null,	-- 用户ID
    score_exp bigint default 0,		-- 积分(经验值)
    vip_level smallint default 0,	-- 会员级别
    virtual_money bigint default 0,	-- 虚拟币
    score_yz bigint default 0,		-- 积分(银子)
    box_yz bigint default 0,		-- 保险箱(银子)
    box_pwd varchar(64),			-- 保险箱密码
    primary key (userid)
)

-- 积分(银子)日志
if not exists(select * from sysobjects where id=object_id('log_scoreyz'))
create table log_scoreyz(
    id int identity(0,1) primary key,	-- 自增ID
    userid varchar(20) not null,		-- 用户ID
    op_type smallint not null,			-- 操作类型(存取)
    op_value bigint not null,			-- 金额
    op_pay int not null,				-- 收取费用
    op_date datetime default getdate(),	-- 操作时间戳
    old_score bigint not null,			-- 原来积分
    new_score bigint not null,			-- 新积分
    old_box bigint not null,			-- 保险箱原值
    new_box bigint not null				-- 保险箱新值
)

-- 充值日志
if not exists(select * from sysobjects where id=object_id('log_pay'))
create table log_pay(
    id int identity(0,1) primary key,	-- 自增ID
    userid varchar(20) not null,		-- 用户ID
    pay_rmb int not null,				-- 充值金额(RMB)
    old_virtualmoney bigint not null,	-- 原来虚拟币
    new_virtualmoney bigint not null,	-- 新虚拟币
    pay_type smallint not null,			-- 充值途径
    pay_date datetime default getdate()	-- 充值时间戳
)

-- 购买虚拟积分(道具)日志
if not exists(select * from sysobjects where id=object_id('log_buyitem'))
create table log_buyitem(
    id int identity(0,1) primary key,	-- 自增ID
    userid varchar(20) not null,		-- 用户ID
    virtual_money int not null,			-- 购买虚拟币
    item_id int not null,				-- 虚拟道具ID
    item_count bigint not null,			-- 购买数量
    buy_date datetime default getdate()	-- 购买时间戳
)


-- --------------------------------------------------
-- 存储过程
-- --------------------------------------------------

--
-- 测试
--
if exists(select * from sysobjects where id=object_id('cp_test'))
	drop procedure cp_test
go
create procedure cp_test @a int, @b int output as
begin
-- b = a + 10;
set @b = @a + 10
end
go


--
-- 注册
--
if exists(select * from sysobjects where id=object_id('cp_user_register'))
	drop procedure cp_user_register
go
create procedure cp_user_register @userid varchar(20),@pwd varchar(32),
    @sx char(2),@realname varchar(20),@idcard varchar(18),@tel varchar(32),
    @email varchar(64),@ip varchar(32),@sn varchar(64),
    @result int output as
begin
-- result:
-- 0 成功
-- 1 此ID已经被注册
-- 2 失败

set @result=2
select * from t_user where userid=@userid
if @@ROWCOUNT <> 0
begin
    set @result=1
    return
end
insert into t_user(userid,password,sex,real_name,identitycard,phone,email,ip_reg,sn_reg)
    values (@userid,@pwd,@sx,@realname,@idcard,@tel,@email,@ip,@sn)

select * from t_userext where userid=@userid
if @@ROWCOUNT = 0
begin
insert into t_userext(userid,score_yz,box_pwd) values(@userid,200,'')
end

set @result=0

end
go

--
-- 保险箱(银子)操作
-- op_type:0 存入 1 去除
--
if exists(select count(*) from sysobjects where id=object_id('cp_box_score'))
	drop procedure cp_box_score
go
create procedure cp_box_score @userid varchar(20),@password varchar(32),
    @type int,@value bigint,
    @result int output as
begin
-- result:
-- 0 成功
-- 1 没有这个用户
-- 2 密码错误
-- 3 不够存入
-- 4 不够取出
-- 5 失败

declare @old_box bigint
declare @new_box bigint
declare @old_yz bigint
declare @new_yz bigint
declare @pwd varchar(64)

set @result=5
select @old_yz=score_yz,@old_box=box_yz,@pwd=box_pwd from t_userext where userid=@userid;
if @@ROWCOUNT = 0
begin
    set @result=1
    return
end

if LEN(@pwd)>0 and @pwd<>@password
begin
    set @result=2
    return
end

if @type=0
begin
    -- 存入
    if @old_yz < @value
	begin
        set @result=3
        return
	end
    set @new_yz=@old_yz-@value
    set @new_box=@old_box+@value
end
if @type=1
begin
    -- 取出
    if @old_box < @value
	begin
        set @result=4
        return
	end
    set @new_yz=@old_yz+@value
    set @new_box=@old_box-@value
end

if @type=0
    update t_userext set score_yz=score_yz-@value,box_yz=box_yz+@value where userid=@userid;
if @type=1
    update t_userext set score_yz=score_yz+@value,box_yz=box_yz-@value where userid=@userid;

insert into log_scoreyz(userid,op_type,op_value,op_pay,old_score,new_score,old_box,new_box)
    values (@userid,@type,@value,0,@old_yz,@new_yz,@old_box,@new_box);

set @result=0

end
go

--
-- 充值
-- flag:0-虚拟币充值,1-直接转换为银子
--
if exists(select * from sysobjects where id=object_id('cp_pay'))
	drop procedure cp_pay
go
create procedure cp_pay @userid varchar(20),@rmb int,
    @flag int,@ptype int,
    @new_vm bigint output,@new_yz bigint output,
    @result int output as
begin
-- result:
-- 0 成功
-- 1 没有这个用户
-- 2 充值金额不符合要求
-- 3 失败

declare @old_vm bigint
declare @old_box bigint

set @result=3
if @rmb > 10000
begin
    set @result=2
    return
end

select @old_vm=virtual_money,@old_box=box_yz from t_userext where userid=@userid;
if @@ROWCOUNT = 0
begin
    set @result=1
    return
end

if @flag=0
begin
    -- 虚拟币
    set @new_vm=@old_vm+@rmb;
    update t_userext set virtual_money=virtual_money+@rmb where userid=@userid
    insert into log_pay(userid,pay_rmb,pay_type,old_virtualmoney,new_virtualmoney)
        values(@userid,@rmb,@ptype,@old_vm,@new_vm)
end
if @flag=1
begin
    -- 虚拟币直接兑换银子
    set @new_yz=@old_box+@rmb*10000
    update t_userext set box_yz=@new_yz where userid=@userid
    insert into log_pay(userid,pay_rmb,pay_type,old_virtualmoney,new_virtualmoney)
        values(@userid,@rmb,@ptype,@old_vm,@old_vm+@rmb)
    insert into log_buyitem(userid,virtual_money,item_id,item_count)
        values(@userid,@rmb,0,@rmb*10000)
end

set @result=0

end
go
